package com.boby.homecamera.db

import android.content.ContentValues
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase


class VideoEventTable(val database:SQLiteDatabase) {

     companion object {
         private   val TABLE_VIDEO_EVENT = "video_event"
         fun createTable(db: SQLiteDatabase) {

             db.execSQL(
                 """
            CREATE TABLE IF NOT EXISTS $TABLE_VIDEO_EVENT (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                eventType INTEGER NOT NULL,
                start_time INTEGER NOT NULL,
                end_time INTEGER NOT NULL,
                threshold FLOAT ,
                des TEXT,
                capturePhoto TEXT,
            );
            """.trimIndent()
             )
         }
          fun upgradeToVersion4(db: SQLiteDatabase) {
              // 不设置默认值的方式
              db.execSQL("ALTER TABLE $TABLE_VIDEO_EVENT ADD COLUMN capturePhoto TEXT")
          }
     }

    // --- 插入 Video 数据 ---
    fun insertVideoEvent(video: VideoEvent): Long {
        val values = ContentValues().apply {
            put("eventType", video.eventType)
            put("start_time", video.startTime)
            put("end_time", video.endTime)
            put("threshold", video.threshold)
            put("des", video.des)
            put("capturePhoto", video.capturePhoto)
        }
        return database.insert(TABLE_VIDEO_EVENT, null, values)
    }

    // --- 插入 Video 数据 ---
    fun updateVideo(video: VideoEvent): Int {
        val values = ContentValues().apply {
            put("eventType", video.eventType)
            put("start_time", video.startTime)
            put("end_time", video.endTime)
            put("threshold", video.threshold)
            put("des", video.des)
            put("capturePhoto", video.capturePhoto)
        }
        return database.update(TABLE_VIDEO_EVENT, values, "id=?", arrayOf(video.id.toString()))
    }


    // --- 查询所有 Video 数据 ---
    fun queryAllVideoEvents(): List<VideoEvent> {
        val videos = mutableListOf<VideoEvent>()
        val cursor: Cursor = database.query(
            TABLE_VIDEO_EVENT,
            arrayOf("id", "eventType","start_time", "end_time", "threshold","des","capturePhoto"),
            null, null, null, null, null
        )
        cursor.use {
            while (it.moveToNext()) {
                val video = VideoEvent(
                    id = it.getLong(it.getColumnIndexOrThrow("id")),
                    eventType = it.getInt(it.getColumnIndexOrThrow("eventType")),
                    startTime = it.getLong(it.getColumnIndexOrThrow("start_time")),
                    endTime =  it.getLong(it.getColumnIndexOrThrow("end_time")),
                    threshold = it.getFloat(it.getColumnIndexOrThrow("threshold")),
                    des = it.getString(it.getColumnIndexOrThrow("des")),
                    capturePhoto = it.getString(it.getColumnIndexOrThrow("capturePhoto"))
                )
                videos.add(video)
            }
        }
        return videos
    }
    // --- 查询所有 Video 数据 --- 23:59 00:00:02
    fun queryVideoEvents(startTime:Long?,endTime:Long?,eventTypes: String?,isASC: Boolean): List<VideoEvent> {
        val videos = mutableListOf<VideoEvent>()
        val cursor: Cursor = if(eventTypes.isNullOrEmpty()){
            database.query(
                TABLE_VIDEO_EVENT,
                arrayOf("id", "eventType", "start_time", "end_time","threshold","des","capturePhoto"),
                "end_time >= ? AND start_time <= ?",  arrayOf(startTime.toString(),endTime.toString()),
                null,
                null,
                "start_time ${if(isASC) "ASC" else "DESC"}"
            )
        }else{
            database.query(
                TABLE_VIDEO_EVENT,
                arrayOf("id", "eventType", "start_time", "end_time","threshold","des","capturePhoto"),
                "end_time >= ? AND start_time <= ? AND eventType IN ($eventTypes)",  arrayOf(startTime.toString(),endTime.toString()),
                null,
                null,
                "start_time ${if(isASC) "ASC" else "DESC"}"
            )
        }
        cursor.use {
            while (it.moveToNext()) {
                val video = VideoEvent(
                    id = it.getLong(it.getColumnIndexOrThrow("id")),
                    eventType = it.getInt(it.getColumnIndexOrThrow("eventType")),
                    startTime = it.getLong(it.getColumnIndexOrThrow("start_time")),
                    endTime =  it.getLong(it.getColumnIndexOrThrow("end_time")),
                    threshold = it.getFloat(it.getColumnIndexOrThrow("threshold")),
                    des = it.getString(it.getColumnIndexOrThrow("des")),
                    capturePhoto = it.getString(it.getColumnIndexOrThrow("capturePhoto")),
                )
                videos.add(video)
            }
        }
        return videos
    }
    fun queryLast(): VideoEvent? {
// "path IS NOT NULL AND path != '' AND start_time IS NOT NULL AND end_time IS NOT NULL AND length > 0",
        val cursor: Cursor = database.query(
            TABLE_VIDEO_EVENT,
            arrayOf("id", "eventType", "start_time", "end_time","threshold", "des","capturePhoto"),
            null,
            null, null, null,
            "start_time DESC", // 可选：按时间倒序拿最近的
            "1" // 只取一条
        )
        cursor.use {
            while (it.moveToFirst()) {
                val video = VideoEvent(
                    id = it.getLong(it.getColumnIndexOrThrow("id")),
                    eventType = it.getInt(it.getColumnIndexOrThrow("eventType")),
                    startTime = it.getLong(it.getColumnIndexOrThrow("start_time")),
                    endTime =  it.getLong(it.getColumnIndexOrThrow("end_time")),
                    threshold = it.getFloat(it.getColumnIndexOrThrow("threshold")),
                            des = it.getString(it.getColumnIndexOrThrow("des")),
                    capturePhoto = it.getString(it.getColumnIndexOrThrow("capturePhoto")),
                )
                return video
            }
        }
        return null
    }



    data class VideoEvent(
        var id: Long = -1,
        var eventType: Int,//1，说话，2，婴儿哭声，3，移动侦测，4，人像
        var startTime: Long,
        var endTime: Long,
        var threshold: Float = 0f,
        var des: String? = null,
        var capturePhoto: String? = null,///抓拍文件
    )
}


